//-------------------------------------------------------------------
//文件名称：EPackage.cs
//模块名称：EPackage数据访问层
//功能说明：
//-----------------------------------------------------------------
//修改记录：
//修改人：Dawen
//-----------------------------------------------------------------

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ELearning.Models;
using ELearning.Models.ViewModel.YTJYAdmin.Course;
using ELearning.Models.ViewModel.YTJYAdmin.Package;

namespace ELearning.DAL
{
    /// <summary>
    /// EPackage数据访问层
    /// </summary>
	public partial class EPackageRepository : BaseRepository<EPackage>
    {
        /// <summary>
        /// 获取套餐列表
        /// </summary>
	    public List<PackageModel> GetModelList(PackageRequest request)
        {
            var args = new List<SqlParameter>();
            var where = string.Empty;
            if (!string.IsNullOrEmpty(request.Name))
                where = $" AND v.Name LIKE N'%{request.Name}%'";

            if (!string.IsNullOrEmpty(request.CategoryId))
            {
                where = $" {where} AND v.CategoryId = @CategoryId";
                args.Add(new SqlParameter("@CategoryId", request.CategoryId));
            }

            if (!string.IsNullOrEmpty(request.TypeCode))
            {
                where = $" {where} AND v.TypeCode = @TypeCode";
                args.Add(new SqlParameter("@TypeCode", request.TypeCode));
            }
            var sql = $@"

                    SELECT
	                    *
                    FROM
	                    (
		                    SELECT
			                    v.*,
			                    d1.TypeName CategoryName,
			                    d2.TypeName ,
			                    ROW_NUMBER() OVER(ORDER BY v.CreateTime DESC)rn,
			                    COUNT(1) OVER() TotalCount
		                    FROM
			                    dbo.EPackage v
			                    LEFT JOIN dbo.EDictionary d1 ON v.CategoryId = d1.TypeCode AND d1.IsDelete = 0
			                    LEFT JOIN dbo.EDictionary d2 ON v.TypeCode = d2.TypeCode AND d2.IsDelete = 0
		                    WHERE
			                    v.IsDelete = 0  AND v.IsUsed = 1 {where}
	                    )a
                    WHERE
	                    a.rn BETWEEN  {request.StartIndex} AND {request.EndIndex}";

            return nContext.Database.SqlQuery<PackageModel>(sql, args.ToArray()).ToList();
        }

        /// <summary>
        /// 获取套餐关联或者不关联课程列表
        /// </summary>
        public List<PackageCourseModel> GetPackageCourseList(PackageCourseRequest request)
        {
            var where = "AND cv.PackageId IS NOT NULL";
            if (request.Type == 0)
                where = $" AND cv.PackageId IS NULL";
            if (!string.IsNullOrEmpty(request.Name))
                where = $" {where}  AND v.Name LIKE N'%{request.Name}%'";

            var sql = $@"
                    SELECT
	                    *
                    FROM
	                    (
		                    SELECT
			                    cv.PackageId,
								v.Id,
								v.Name,
								v.Description,
			                    d1.TypeName CategoryName,
			                    d2.TypeName ,
			                    ROW_NUMBER() OVER(ORDER BY v.CreateTime DESC)rn,
			                    COUNT(1) OVER() TotalCount
		                    FROM
			                    dbo.ECourse v
								LEFT JOIN dbo.RPackageCourse cv ON cv.CourseId = v.Id AND cv.IsDelete = 0  AND cv.PackageId = '{request.PackageId}'
			                    LEFT JOIN dbo.EDictionary d1 ON v.CategoryId = d1.TypeCode AND d1.IsDelete = 0
			                    LEFT JOIN dbo.EDictionary d2 ON v.TypeCode = d2.TypeCode AND d2.IsDelete = 0
		                    WHERE
			                    v.IsDelete = 0  {where}
	                    )a
                    WHERE
	                    a.rn BETWEEN  {request.StartIndex} AND {request.EndIndex}";

            return nContext.Database.SqlQuery<PackageCourseModel>(sql).ToList();
        }

        /// <summary>
        /// 获取套餐关联或者不关联的用户列表
        /// </summary>
        public List<PackageUserModel> GetPackageUserList(PackageUserRequest request)
        {
            var where = "AND up.PackageId IS NOT NULL";
            if (request.Type == 0)
                where = $" AND up.PackageId IS NULL";
            if (!string.IsNullOrEmpty(request.UserName))
                where = $" {where}  AND u.UserName LIKE N'%{request.UserName}%'";
            if (!string.IsNullOrEmpty(request.LoginName))
                where = $" {where}  AND u.LoginName LIKE N'%{request.LoginName}%'";

            var sql = $@"
                    SELECT
	                    *
                    FROM
	                    (
		                    SELECT
			                    u.UserID,			
			                    ROW_NUMBER() OVER(ORDER BY u.CreateTime DESC) rn,
			                    COUNT(1) OVER() TotalCount,
			                    u.UserName,
			                    u.LoginName,
			                    u.MobilePhone
		                    FROM
			                    dbo.ESysUser u
			                    LEFT JOIN dbo.TUserPackage up ON u.UserID = up.UserId AND up.IsDelete = 0 AND up.PackageId = @PackageId
		                    WHERE
			                    u.IsDelete = 0  {where}
	                    )a
                    WHERE
	                    a.rn BETWEEN  {request.StartIndex} AND {request.EndIndex}";
            var args = new List<SqlParameter>
            {
                new SqlParameter("@PackageId",request.PackageId)
            };

            return nContext.Database.SqlQuery<PackageUserModel>(sql, args.ToArray()).ToList();
        }

        /// <summary>
        /// 获取套餐列表
        /// </summary>
        public List<SitePackageModel> GetSiteModelList(SitePackageRequest request, Guid userId)
        {
            var args = new List<SqlParameter>();
            var where = string.Empty;
            if (!string.IsNullOrEmpty(request.Name))
                where = $" AND v.Name LIKE N'%{request.Name}%'";

            if (!string.IsNullOrEmpty(request.CategoryId))
            {
                where = $" {where} AND v.CategoryId = @CategoryId";
                args.Add(new SqlParameter("@CategoryId", request.CategoryId));
            }

            if (!string.IsNullOrEmpty(request.TypeCode))
            {
                where = $" {where} AND v.TypeCode = @TypeCode";
                args.Add(new SqlParameter("@TypeCode", request.TypeCode));
            }
            var sql = $@"

                    SELECT
	                    *
                    FROM
	                    (
		                    SELECT
			                    v.*,
			                    d1.TypeName CategoryName,
			                    d2.TypeName ,
			                    ROW_NUMBER() OVER(ORDER BY v.CreateTime DESC)rn,
			                    COUNT(1) OVER() TotalCount,
								(SELECT COUNT(1) FROM dbo.TUserPackage up WHERE up.IsDelete = 0 AND up.PackageId = v.Id)EnrollCount,
								(SELECT COUNT(1) FROM dbo.TUserPackage up WHERE up.IsDelete = 0 AND up.UserId = @userId AND up.PackageId = v.Id)BuyCount
		                    FROM
			                    dbo.EPackage v
			                    LEFT JOIN dbo.EDictionary d1 ON v.CategoryId = d1.TypeCode AND d1.IsDelete = 0
			                    LEFT JOIN dbo.EDictionary d2 ON v.TypeCode = d2.TypeCode AND d2.IsDelete = 0
		                    WHERE
			                    v.IsDelete = 0  AND v.IsUsed = 1 {where}
	                    )a
                    WHERE
	                    a.rn BETWEEN  {request.StartIndex} AND {request.EndIndex}";

            args.Add(new SqlParameter("@userId", userId));

            return nContext.Database.SqlQuery<SitePackageModel>(sql, args.ToArray()).ToList();
        }

        /// <summary>
        /// 获取用户套餐列表
        /// </summary>
        /// <param name="userId">用户id</param>
        public List<UserPackageModel> GetUserModelList(Guid userId)
        {
            var sql = $@"
                SELECT
	                p.Id,
	                p.Name,
	                p.Description,
	                p.CoverUrl
                FROM
	                dbo.TUserPackage up
	                LEFT JOIN dbo.EPackage p ON up.PackageId = p.Id
                WHERE
	                up.IsDelete = 0 AND p.IsDelete = 0
	                AND up.UserId = @userId";

            var args = new List<SqlParameter>
            {
                new SqlParameter("@userId",userId)
            };

            return nContext.Database.SqlQuery<UserPackageModel>(sql, args.ToArray()).ToList();
        }
    }
}


